﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using Lists;
using BasicForms;


namespace ReportGenerators
{
    public partial class FrmThamSoBaoCaoKinhDoanhLaiLo : BasicForms.FrmThamSoBaoCao
    {
        public FrmThamSoBaoCaoKinhDoanhLaiLo()
        {
            InitializeComponent();
        }

        protected override void PrepareDataForReport()
        {
            BasicForms.FrmProgressBar.StartShowProgress();

            SqlConnection con = new SqlConnection(AccountingLibs.LocalSettings.ConnectString);
            ReportTemplates.Khac.DsBaoCaoKinhDoanhLaiLo ds = new ReportTemplates.Khac.DsBaoCaoKinhDoanhLaiLo();
            try
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                SqlDataAdapter da = null;

                cmd.CommandText = "Select_ThongTinDoanhNghiep2";
                cmd.Parameters.Clear();
                da = new SqlDataAdapter(cmd);
                da.Fill(ds.ThongTinDoanhNghiep);

                ds.ChiTietBaoCaoKinhDoanhLaiLo.Rows.Add(ds.ChiTietBaoCaoKinhDoanhLaiLo.NewRow());
                ds.ChiTietBaoCaoKinhDoanhLaiLo.Rows[0]["KyBaoCao"] = _KyBaoCao.Ten;

                DataTable dtMuaHang = new DataTable();
                DataTable dtHangMuaTraLai = new DataTable();
                DataTable dtBanHang = new DataTable();
                DataTable dtHangBanTraLai = new DataTable();

                #region GetData MuaHang
                cmd.CommandText = "SELECT C.VatTuHangHoa1ID VatTuHangHoaID, SUM(C.Value1) SoLuong, SUM(C.Value5) ThanhTien \n" +
                                "FROM ChungTu A INNER JOIN ChungTu B ON A.ChungTuID = B.ChungTuGocID \n" +
                                "INNER JOIN KhoanMucChungTu C ON B.ChungTuID = C.ChungTuID \n" +
                                "WHERE A.LoaiChungTuID IN (SELECT ID \n" +
                                "                           FROM LoaiChungTu \n" +
                                "                           WHERE Ma = 'HoaDonMuaLe' OR Ma ='HoaDonMuaHang') \n" +
                                "       AND A.GhiSo = 1  \n" +
                                "       AND @ThoiDiemDau <= A.Ngay AND A.Ngay <= @ThoiDiemCuoi  \n" +
                                "GROUP BY C.VatTuHangHoa1ID	\n" +
                                "HAVING SUM(C.Value1) IS NOT NULL AND SUM(C.Value5) IS NOT NULL";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("ThoiDiemDau", _KyBaoCao.ThoiDiemDau);
                cmd.Parameters.AddWithValue("ThoiDiemCuoi", _KyBaoCao.ThoiDiemCuoi);
                da = new SqlDataAdapter(cmd);
                da.Fill(dtMuaHang);
                #endregion
                #region GetData HangMuaTraLai
                cmd.CommandText = "SELECT C.VatTuHangHoa1ID VatTuHangHoaID, SUM(C.Value1) SoLuong, SUM(C.Value5) ThanhTien \n" +
                                "FROM ChungTu A INNER JOIN ChungTu B ON A.ChungTuID = B.ChungTuGocID \n" +
                                "INNER JOIN KhoanMucChungTu C ON B.ChungTuID = C.ChungTuID \n" +
                                "WHERE A.LoaiChungTuID IN (SELECT ID \n" +
                                "                           FROM LoaiChungTu \n" +
                                "                           WHERE Ma = 'HangMuaTraLai') \n" +
                                "       AND A.GhiSo = 1  \n" +
                                "       AND @ThoiDiemDau <= A.Ngay AND A.Ngay <= @ThoiDiemCuoi  \n" +
                                "GROUP BY C.VatTuHangHoa1ID	\n" +
                                "HAVING SUM(C.Value1) IS NOT NULL AND SUM(C.Value5) IS NOT NULL";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("ThoiDiemDau", _KyBaoCao.ThoiDiemDau);
                cmd.Parameters.AddWithValue("ThoiDiemCuoi", _KyBaoCao.ThoiDiemCuoi);
                da = new SqlDataAdapter(cmd);
                da.Fill(dtHangMuaTraLai);
                #endregion
                #region GetData BanHang
                cmd.CommandText = "SELECT C.VatTuHangHoa1ID VatTuHangHoaID, SUM(C.Value1) SoLuong, SUM(C.Value5) ThanhTien, SUM(C.Value8 * C.Value13) TienChietKhau, SUM(C.Value5 - C.Value3)/100 TienLai \n" +
                                "FROM ChungTu A INNER JOIN ChungTu B ON A.ChungTuID = B.ChungTuGocID \n" +
                                "INNER JOIN KhoanMucChungTu C ON B.ChungTuID = C.ChungTuID \n" +
                                "WHERE A.LoaiChungTuID IN (SELECT ID \n" +
                                "                           FROM LoaiChungTu \n" +
                                "                           WHERE Ma = 'HoaDonBanLe' OR Ma ='HoaDonBanHang') \n" +
                                "       AND A.GhiSo = 1  \n" +
                                "       AND @ThoiDiemDau <= A.Ngay AND A.Ngay <= @ThoiDiemCuoi  \n" +
                                "GROUP BY C.VatTuHangHoa1ID	\n" +
                                "HAVING SUM(C.Value1) IS NOT NULL AND SUM(C.Value5) IS NOT NULL";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("ThoiDiemDau", _KyBaoCao.ThoiDiemDau);
                cmd.Parameters.AddWithValue("ThoiDiemCuoi", _KyBaoCao.ThoiDiemCuoi);
                da = new SqlDataAdapter(cmd);
                da.Fill(dtBanHang);
                #endregion
                #region GetData HangBanTraLai
                cmd.CommandText = "SELECT C.VatTuHangHoa1ID VatTuHangHoaID, SUM(C.Value1) SoLuong, SUM(C.Value5) ThanhTien, SUM(C.Value5 - C.Value3) TienGiamLai \n" +
                                "FROM ChungTu A INNER JOIN ChungTu B ON A.ChungTuID = B.ChungTuGocID \n" +
                                "INNER JOIN KhoanMucChungTu C ON B.ChungTuID = C.ChungTuID \n" +
                                "WHERE A.LoaiChungTuID IN (SELECT ID \n" +
                                "                           FROM LoaiChungTu \n" +
                                "                           WHERE Ma = 'HangBanTraLai') \n" +
                                "       AND A.GhiSo = 1  \n" +
                                "       AND @ThoiDiemDau <= A.Ngay AND A.Ngay <= @ThoiDiemCuoi  \n" +
                                "GROUP BY C.VatTuHangHoa1ID	\n" +
                                "HAVING SUM(C.Value1) IS NOT NULL AND SUM(C.Value5) IS NOT NULL";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("ThoiDiemDau", _KyBaoCao.ThoiDiemDau);
                cmd.Parameters.AddWithValue("ThoiDiemCuoi", _KyBaoCao.ThoiDiemCuoi);
                da = new SqlDataAdapter(cmd);
                da.Fill(dtHangBanTraLai);
                #endregion

                int count = 0;
                DanhMucVatTuHangHoa danhMucHang = DanhMucVatTuHangHoa.GetDanhMucHangHoa();
                foreach (VatTuHangHoa hangHoa in danhMucHang)
                {
                    BasicForms.FrmProgressBar.Status = string.Format("Đang tính {0}/{1}", ++count, danhMucHang.Count);
                    if (count >= 10) break;

                    DataRow row = ds.KhoanMucBaoCaoKinhDoanhLaiLo.NewRow();
                    ds.KhoanMucBaoCaoKinhDoanhLaiLo.Rows.Add(row);

                    row["TenNhomVatTuHangHoa"] = hangHoa.NhomVatTuHangHoa.Ten;
                    row["MaVatTuHangHoa"] = hangHoa.Ma;
                    row["TenVatTuHangHoa"] = hangHoa.Ten;

                    TonKho tonDauKy = TonKho.CalculateFromDB(_KyBaoCao.ThoiDiemDau, hangHoa.ID);
                    row["SoLuongTonDauKy"] = tonDauKy.SoLuong;
                    row["GiaTriTonDauKy"] = tonDauKy.ThanhTien;

                    DataRow[] rowsMuaHang = dtMuaHang.Select(string.Format("VatTuHangHoaID = '{0}'", hangHoa.ID));
                    if(rowsMuaHang.Length == 1) 
                    {
                        if (rowsMuaHang[0]["SoLuong"] is decimal && rowsMuaHang[0]["ThanhTien"] is decimal)
                        {
                            row["SoLuongMua"] = (decimal)rowsMuaHang[0]["SoLuong"];
                            row["GiaTriMua"] = (decimal)rowsMuaHang[0]["ThanhTien"];
                        }
                    }

                    DataRow[] rowsHangMuaTraLai = dtHangMuaTraLai.Select(string.Format("VatTuHangHoaID = '{0}'", hangHoa.ID));
                    if(rowsHangMuaTraLai.Length == 1) 
                    {
                        if (rowsHangMuaTraLai[0]["SoLuong"] is decimal && rowsHangMuaTraLai[0]["ThanhTien"] is decimal)
                        {
                            row["SoLuongHangMuaTraLai"] = (decimal)rowsHangMuaTraLai[0]["SoLuong"];
                            row["GiaTriHangMuaTraLai"] = (decimal)rowsHangMuaTraLai[0]["ThanhTien"];
                        }
                    }

                    DataRow[] rowsBanHang = dtBanHang.Select(string.Format("VatTuHangHoaID = '{0}'", hangHoa.ID));
                    if(rowsBanHang.Length == 1) 
                    {
                        if (rowsBanHang[0]["SoLuong"] is decimal && rowsBanHang[0]["ThanhTien"] is decimal)
                        {
                            row["SoLuongBan"] = (decimal)rowsBanHang[0]["SoLuong"];
                            row["GiaTriBan"] = (decimal)rowsBanHang[0]["ThanhTien"];
                        }
                        if (rowsBanHang[0]["TienChietKhau"] is decimal)
                        {
                            row["TongTienChietKhau"] = (decimal)rowsBanHang[0]["TienChietKhau"];
                        }
                        if (rowsBanHang[0]["TienLai"] is decimal)
                        {
                            row["LaiGop"] = (decimal)row["LaiGop"] + (decimal)rowsBanHang[0]["TienLai"];
                        }
                    }

                    DataRow[] rowsHangBanTraLai = dtHangBanTraLai.Select(string.Format("VatTuHangHoaID = '{0}'", hangHoa.ID));
                    if(rowsHangBanTraLai.Length == 1) 
                    {
                        if (rowsHangBanTraLai[0]["SoLuong"] is decimal && rowsHangBanTraLai[0]["ThanhTien"] is decimal)
                        {
                            row["SoLuongHangBanTraLai"] = (decimal)rowsHangBanTraLai[0]["SoLuong"];
                            row["GiaTriHangBanTraLai"] = (decimal)rowsHangBanTraLai[0]["ThanhTien"];
                        }
                        if (rowsHangBanTraLai[0]["TienGiamLai"] is decimal)
                        {
                            row["LaiGop"] = (decimal)row["LaiGop"] - (decimal)rowsHangBanTraLai[0]["TienGiamLai"];
                        }
                    }

                    row["SoLuongTonCuoi"] = tonDauKy.SoLuong + (decimal)row["SoLuongMua"] - (decimal)row["SoLuongHangMuaTraLai"]
                                                                - (decimal)row["SoLuongBan"] + -(decimal)row["SoLuongHangBanTraLai"];
                    row["GiaTriTonCuoi"] = tonDauKy.ThanhTien + (decimal)row["GiaTriMua"] - (decimal)row["GiaTriHangMuaTraLai"]
                                            - (decimal)row["GiaTriBan"] + -(decimal)row["GiaTriHangBanTraLai"];
                }

                if (ds.Tables.Count > 0)
                {
                    _Report = new ReportTemplates.Khac.RptBaoCaoKinhDoanhLaiLo();
                    _Report.SetDataSource(ds);
                }
            }
            catch (Exception ex)
            {
            }
            finally
            {
                con.Close();
                con = null;
            }

            BasicForms.FrmProgressBar.FinishShowProgress();
        }

        protected override void PrintReport()
        {
            if (_Report != null)
            {
                ReportTemplates.FrmPrintReport frm = new ReportTemplates.FrmPrintReport(_Report, "Báo cáo tính hình mua bán lãi lỗ");
                frm.Show();
            }
        }
    }
}
